using System;
using System.Collections.Generic;
using System.Text;
using Customs.Components.KDT.GC.Info;
using Customs.Components;
using System.Data;
using System.Data.SqlClient;
using System.Configuration;
using Microsoft.ApplicationBlocks.Data;
namespace Customs.Components.KDT.GC
{
    public class ThietBi : ThietBiInfo
    {
        public bool load()
        {
            SqlDataReader reader = this.Select();
            if (reader.Read())
            {
                if (!reader.IsDBNull(reader.GetOrdinal("SoHopDong"))) this.SoHopDong = reader.GetString(reader.GetOrdinal("SoHopDong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) this.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) this.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKy"))) this.NgayKy = reader.GetDateTime(reader.GetOrdinal("NgayKy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaTB"))) this._Ma = reader.GetString(reader.GetOrdinal("MaTB"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHS"))) this.MaHS = reader.GetString(reader.GetOrdinal("MaHS"));
                if (!reader.IsDBNull(reader.GetOrdinal("TenTB"))) this._Ten = reader.GetString(reader.GetOrdinal("TenTB"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuong"))) this._SoLuong = reader.GetDecimal(reader.GetOrdinal("SoLuong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDVT"))) this._DVT_ID = reader.GetString(reader.GetOrdinal("MaDVT"));
                if (!reader.IsDBNull(reader.GetOrdinal("XuatXu"))) this.NuocXX_ID = reader.GetString(reader.GetOrdinal("XuatXu"));
                if (!reader.IsDBNull(reader.GetOrdinal("TinhTrang"))) this.TinhTrang = reader.GetString(reader.GetOrdinal("TinhTrang"));
                if (!reader.IsDBNull(reader.GetOrdinal("DonGia"))) this.DonGia = reader.GetDecimal(reader.GetOrdinal("DonGia"));
                if (!reader.IsDBNull(reader.GetOrdinal("TriGia"))) this._TriGia = reader.GetDecimal(reader.GetOrdinal("TriGia"));
                if (!reader.IsDBNull(reader.GetOrdinal("NguyenTe"))) this._NguyenTe_ID = reader.GetString(reader.GetOrdinal("NguyenTe"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) this.STTHang = reader.GetInt32(reader.GetOrdinal("STTHang"));
                reader.Close();
                return true;
            }
            reader.Close();
            return false;
        }
        public void Load(ThietBiInfo tb)
        {
            this.SoHopDong = tb.SoHopDong;
            this._MaDoanhNghiep = tb.MaDoanhNghiep;
            this._MaHaiQuan = tb.MaHaiQuan;
            this.NgayKy = tb.NgayKy;
            this.SoLuong = tb.SoLuong;
            this.STTHang = tb.STTHang;
            this.Ma = tb.Ma;
            this.MaHS = tb.MaHS;
            this._Ten = tb.Ten;
            this.TinhTrang = tb.TinhTrang;
            this._TriGia = tb.TriGia;
            this.NuocXX_ID = tb.NuocXX_ID;
            this.NguyenTe_ID = tb.NguyenTe_ID;            
            this.STTHang = tb.STTHang;
            this._DVT_ID = tb.DVT_ID;
        }
        public SqlDataReader Select()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_HD AS [SoHopDong], ");
            query.Append("Ma_HQHD AS [MaHaiQuan], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [NgayKy], ");
            query.Append("P_Code AS [MaSP], ");
            query.Append("HS_Code AS [MaHS], ");
            query.Append("Ten_TB AS [TenTB], ");
            query.Append("SL_DK AS [SoLuong], ");
            query.Append("Ma_DVT AS [MaDVT], ");
            query.Append("Xuat_Xu AS [XuatXu], ");
            query.Append("TinhTrang AS [TinhTrang], ");
            query.Append("TriGia AS [TriGia], ");
            query.Append("NGTe AS [NguyetTe], ");
            query.Append("STTHang AS [STTHang], ");
            query.Append("DonGia AS [DonGia] ");
            query.Append("FROM dbo.[tblDTHIETBI] ");
            query.Append("WHERE ");
            query.Append("So_HD = @SoHopDong AND Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep and ngay_ky=@NgayKy and P_Code=@MaTB");

            SqlParameter[] parameters = new SqlParameter[5];
            parameters[0] = new SqlParameter("@SoHopDong", SqlDbType.VarChar);
            parameters[0].Value = this._SoHopDong;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char,6);
            parameters[1].Value = this._MaHaiQuan;

            parameters[2] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[2].Value = this._MaDoanhNghiep;

            parameters[3] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            parameters[3].Value = this._NgayKy;

            parameters[4] = new SqlParameter("@MaTB", SqlDbType.VarChar);
            parameters[4].Value = this.Ma;
            return SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
        }

        //---------------------------------------------------------------------------------------------
        public int Delete(SqlTransaction transaction)
        {
            string query = string.Format("DELETE FROM tblDTHIETBI WHERE So_HD = '{0}' AND Ma_HQHD = '{1}' AND DVGC = '{2}' and ngay_ky='{3}' and P_Code='{4}'", this.SoHopDong, this.MaHaiQuan, this.MaDoanhNghiep, this.NgayKy, this.Ma);
            return SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query);
        }
        public int Delete()
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            string query = string.Format("DELETE FROM tblDTHIETBI WHERE So_HD = '{0}' AND Ma_HQHD = '{1}' AND DVGC = '{2}' and ngay_ky='{3}' and P_Code='{4}'", this.SoHopDong, this.MaHaiQuan, this.MaDoanhNghiep, this.NgayKy, this.Ma); 
            return SqlHelper.ExecuteNonQuery(connectionString, CommandType.Text, query);
        }
        //---------------------------------------------------------------------------------------------
        public void Delete(SqlTransaction transaction, ThietBiInfoCollection collection)
        {
            ThietBi tb = new ThietBi();
            foreach (ThietBiInfo itemInfo in collection)
            {
                tb.Load(itemInfo);
                tb.Delete();
            }
        }
        //---------------------------------------------------------------------------------------------
        public bool Delete(ThietBiInfoCollection collection)
        {
            bool returnValue;
            using (SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString))
            {
                conn.Open();
                SqlTransaction transaction = conn.BeginTransaction();

                try
                {
                    this.Delete(transaction, collection);
                    transaction.Commit();
                    returnValue = true;
                }
                catch
                {
                    returnValue = false;
                    transaction.Rollback();
                }
                finally
                {
                    conn.Close();
                }
            }
            return returnValue;
        }

        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction, ThietBiInfoCollection collection)
        {
            ThietBi tb = new ThietBi();
            foreach (ThietBiInfo itemInfo in collection)
            {
                tb.Load(itemInfo);
                tb.InsertTransaction(transaction);
            }
        }

        //---------------------------------------------------------------------------------------------
        public void InsertTransaction(SqlTransaction transaction)
        {
            StringBuilder query = new StringBuilder();
            query.Append("INSERT INTO tblDTHIETBI ( ");
            query.Append("So_HD , ");
            query.Append("Ma_HQHD , ");
            query.Append("DVGC , ");
            query.Append("Ngay_Ky , ");
            query.Append("P_Code , ");
            query.Append("HS_Code , ");
            query.Append("Ten_TB , ");
            query.Append("SL_DK , ");
            query.Append("Ma_DVT , ");

            query.Append("Xuat_Xu , ");
            query.Append("TinhTrang , ");
            query.Append("DonGia , ");
            query.Append("TriGia , ");
            query.Append("NGTe , ");            
            query.Append("Ngay_VM , ");
          
            query.Append("STTHang ) ");

            query.Append("VALUES ( ");

            query.Append("@So_HD, ");
            query.Append("@Ma_HQHD, ");
            query.Append("@DVGC, ");
            query.Append("@Ngay_Ky, ");
            query.Append("@P_Code, ");
            query.Append("@HS_Code, ");
            query.Append("@Ten_TB, ");
            query.Append("@SL_DK, ");
            query.Append("@Ma_DVT , ");
            query.Append("@Xuat_Xu , ");
            query.Append("@TinhTrang , ");
            query.Append("@DonGia , ");
            query.Append("@TriGia , ");
            query.Append("@NGTe , ");            
           
            query.Append("@Ngay_VM , ");            
            query.Append("@STTHang )");

            SqlParameter[] parameters = new SqlParameter[16];

            parameters[0] = new SqlParameter("@So_HD", SqlDbType.VarChar);
            parameters[0].Value = this.SoHopDong;

            parameters[1] = new SqlParameter("@Ma_HQHD", SqlDbType.Char, 6);
            parameters[1].Value = this.MaHaiQuan;

            parameters[2] = new SqlParameter("@DVGC", SqlDbType.VarChar);
            parameters[2].Value = this.MaDoanhNghiep;

            parameters[3] = new SqlParameter("@Ngay_Ky", SqlDbType.DateTime);
            parameters[3].Value = this.NgayKy;

            parameters[4] = new SqlParameter("@P_Code", SqlDbType.VarChar);
            parameters[4].Value = this.Ma;

            parameters[5] = new SqlParameter("@HS_Code", SqlDbType.VarChar);
            parameters[5].Value = this._MaHS;

            parameters[6] = new SqlParameter("@Ten_TB", SqlDbType.NVarChar);
            parameters[6].Value = this.Ten;

            parameters[7] = new SqlParameter("@SL_DK", SqlDbType.Decimal);
            parameters[7].Value = this._SoLuong;

            parameters[8] = new SqlParameter("@Ma_DVT", SqlDbType.Char, 3);
            parameters[8].Value = this._DVT_ID;

            parameters[9] = new SqlParameter("@Xuat_Xu", SqlDbType.Char,3);
            parameters[9].Value = this.NuocXX_ID;

            parameters[10] = new SqlParameter("@TinhTrang", SqlDbType.NVarChar);
            parameters[10].Value = this._TinhTrang;

            parameters[11] = new SqlParameter("@DonGia", SqlDbType.Money);
            parameters[11].Value = this._DonGia;

            parameters[12] = new SqlParameter("@TriGia", SqlDbType.Money);
            parameters[12].Value = this._TriGia;


            parameters[13] = new SqlParameter("@Ngay_VM", SqlDbType.DateTime);
            parameters[13].Value = DateTime.Today;

           
            parameters[14] = new SqlParameter("@STTHang", SqlDbType.Int);
            parameters[14].Value = this.STTHang;

            parameters[15] = new SqlParameter("@NGTe", SqlDbType.Char,3);
            parameters[15].Value = this._NguyenTe_ID;

            SqlHelper.ExecuteNonQuery(transaction, CommandType.Text, query.ToString(), parameters);
        }
        public static ThietBiInfoCollection selectThietBiInfoCollectionByHopDong(string mahaiquan, string madoanhnghiep, string sohopdong, DateTime ngayky)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString_KDT"].ConnectionString;
            StringBuilder query = new StringBuilder();
            query.Append("SELECT ");
            query.Append("So_HD AS [SoHopDong], ");
            query.Append("Ma_HQHD AS [MaHaiQuan], ");
            query.Append("DVGC AS [MaDoanhNghiep], ");
            query.Append("Ngay_Ky AS [NgayKy], ");
            query.Append("P_Code AS [MaTB], ");
            query.Append("HS_Code AS [MaHS], ");
            query.Append("Ten_TB AS [TenTB], ");
            query.Append("SL_DK AS [SoLuong], ");
            query.Append("Ma_DVT AS [MaDVT], ");
            query.Append("Xuat_Xu AS [XuatXu], ");
            query.Append("TinhTrang AS [TinhTrang], ");
            query.Append("TriGia AS [TriGia], ");
            query.Append("NGTe AS [NguyenTe], ");
            query.Append("STTHang AS [STTHang], ");
            query.Append("DonGia AS [DonGia] ");
            query.Append("FROM dbo.[tblDTHIETBI] ");
            query.Append("WHERE ");
            query.Append("So_HD = @SoHopDong AND Ma_HQHD = @MaHaiQuan AND DVGC = @MaDoanhNghiep and ngay_ky=@NgayKy ");

            SqlParameter[] parameters = new SqlParameter[4];
            parameters[0] = new SqlParameter("@SoHopDong", SqlDbType.VarChar);
            parameters[0].Value = sohopdong;

            parameters[1] = new SqlParameter("@MaHaiQuan", SqlDbType.Char, 6);
            parameters[1].Value = mahaiquan;

            parameters[2] = new SqlParameter("@MaDoanhNghiep", SqlDbType.VarChar);
            parameters[2].Value = madoanhnghiep;

            parameters[3] = new SqlParameter("@NgayKy", SqlDbType.DateTime);
            parameters[3].Value = ngayky;

            IDataReader reader = SqlHelper.ExecuteReader(connectionString, CommandType.Text, query.ToString(), parameters);
            ThietBiInfoCollection tbCollection = new ThietBiInfoCollection();
            while (reader.Read())
            {
                ThietBiInfo tbInfo = new ThietBiInfo();
                if (!reader.IsDBNull(reader.GetOrdinal("SoHopDong"))) tbInfo.SoHopDong = reader.GetString(reader.GetOrdinal("SoHopDong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHaiQuan"))) tbInfo.MaHaiQuan = reader.GetString(reader.GetOrdinal("MaHaiQuan"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDoanhNghiep"))) tbInfo.MaDoanhNghiep = reader.GetString(reader.GetOrdinal("MaDoanhNghiep"));
                if (!reader.IsDBNull(reader.GetOrdinal("NgayKy"))) tbInfo.NgayKy = reader.GetDateTime(reader.GetOrdinal("NgayKy"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaTB"))) tbInfo.Ma = reader.GetString(reader.GetOrdinal("MaTB"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaHS"))) tbInfo.MaHS = reader.GetString(reader.GetOrdinal("MaHS"));
                if (!reader.IsDBNull(reader.GetOrdinal("TenTB"))) tbInfo.Ten = reader.GetString(reader.GetOrdinal("TenTB"));
                if (!reader.IsDBNull(reader.GetOrdinal("SoLuong"))) tbInfo.SoLuong = reader.GetDecimal(reader.GetOrdinal("SoLuong"));
                if (!reader.IsDBNull(reader.GetOrdinal("MaDVT"))) tbInfo.DVT_ID = reader.GetString(reader.GetOrdinal("MaDVT"));
                if (!reader.IsDBNull(reader.GetOrdinal("XuatXu"))) tbInfo.NuocXX_ID = reader.GetString(reader.GetOrdinal("XuatXu"));
                if (!reader.IsDBNull(reader.GetOrdinal("TinhTrang"))) tbInfo.TinhTrang = reader.GetString(reader.GetOrdinal("TinhTrang"));
                if (!reader.IsDBNull(reader.GetOrdinal("DonGia"))) tbInfo.DonGia = reader.GetDecimal(reader.GetOrdinal("DonGia"));
                if (!reader.IsDBNull(reader.GetOrdinal("TriGia"))) tbInfo.TriGia = reader.GetDecimal(reader.GetOrdinal("TriGia"));
                if (!reader.IsDBNull(reader.GetOrdinal("NguyenTe"))) tbInfo.NguyenTe_ID = reader.GetString(reader.GetOrdinal("NguyenTe"));
                if (!reader.IsDBNull(reader.GetOrdinal("STTHang"))) tbInfo.STTHang = reader.GetInt32(reader.GetOrdinal("STTHang"));
                tbCollection.Add(tbInfo);
            }
            reader.Close();
            return tbCollection;
        }
  
    }
}
